package com.skycode.dao;

import com.skycode.util.DBConnection;
import  com.skycode.bean.NewsBean;
import com.skycode.util.Time;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

// 封装数据库的增删改查
public class NewsDao {
    DBConnection db = new DBConnection();
    public int add(NewsBean news){
        Connection conn = db.getConn();//conn代表数据库链接
        String sql = "insert into news values(null,?,?,?,?)";//使用jdbc预处理对象添加
        try {
            //将新闻数据对象中数据一次绑定打sql命令
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1,news.getNewsTitle());
            ps.setString(2,news.getNewsContent());
            ps.setString(3, Time.getDateTime());//时间优化
            ps.setString(4,news.getAdminName());
            return ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println("插入news表异常");
        }
        return 0;//如果添加异常返回0
    }
//    删除
    public int delete(int NewsID){
        Connection conn = db.getConn();//conn代表数据库链接
        String sql = "delete from news where NewsID=?";//使用jdbc预处理对象添加
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1,NewsID);
            return  ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println("删除news表异常");
        }
        return 0;
    }
    //更新
    public int update(NewsBean news){
        Connection conn = db.getConn();//conn代表数据库链接
        String sql = "update news set NewsTitle=?,NewsContent=?,NewsTime=?,AdminName=? where NewsID=?";//使用jdbc预处理对象添加
        try {
            //将新闻数据对象中数据一次绑定打sql命令
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1,news.getNewsTitle());
            ps.setString(2,news.getNewsContent());
            ps.setString(3,Time.getDateTime());//时间优化
            ps.setString(4,news.getAdminName());
            ps.setInt(5,news.getNewsID());
            return ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println("更新news表异常");
        }
        return 0;//如果添加异常返回0
    }
//    获取新闻数据
//    page 传递进来的页码 最小是1
    public List<NewsBean> getList(int page){
        Connection conn = db.getConn();//conn代表数据库链接
        String sql = "select *from news limit ?,?";//第一个开始的行数 第二个是查询显示的条数
        List<NewsBean> list = new ArrayList<>();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1,(page-1)*10);//page为1是行数为0 page为2时 10行
            ps.setInt(2,10);// 10 表示10条数
            ResultSet rs = ps.executeQuery();//rs 是数据查询结果集 全部是字符串
            while (rs.next()){
                NewsBean news =new NewsBean();//每轮循环生命一个新闻对象
                news .setNewsID(rs.getInt("NewsID"));//rs 获取该行新闻ID 通过新闻对象封装方法设置新闻值
                news .setNewsTitle(rs.getString("NewsTitle"));
                news .setNewsContent(rs.getString("NewsContent"));
                news .setNewsTime(rs.getString("NewsTime"));
                news .setAdminName(rs.getString("AdminName"));
                list.add(news);//将设置好的新闻对象放入list中
            }
        } catch (SQLException e) {
            System.out.println("查询news表异常");
        }
        return list;//如果添加异常返回空
    }


    public int count() {
        Connection conn = db.getConn();
        String sql = "select count(*) from news";
        try{
            Statement statement = conn.createStatement();
            ResultSet rs =statement.executeQuery(sql);
            if (rs.next()) {
                return rs.getInt(1);
            }
        }catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return 0;
    }
    public  NewsBean getOne(int id){
        Connection conn = db.getConn();
        String sql = "select * from news where NewsID=?";
        NewsBean news = new NewsBean();
        try {
            PreparedStatement pmst  = conn.prepareStatement(sql);
            pmst.setInt(1,id);
            ResultSet rs = pmst.executeQuery();
            if (rs.next()){
                news.setNewsID(rs.getInt("NewsID"));
                news.setNewsTitle(rs.getString("NewsTitle"));
                news.setNewsContent(rs.getString("NewsContent"));
                news.setNewsTime(rs.getString("NewsTime"));
                news.setAdminName(rs.getString("AdminName"));
            }
        }catch (SQLException e){
            throw new RuntimeException(e);
        }
        return news;
    }
}
